﻿USE [iKBSystem]
GO

----- sp_AddOrUpdateThirdPartyUrl -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddOrUpdateThirdPartyUrl]'))
DROP PROCEDURE [dbo].[sp_AddOrUpdateThirdPartyUrl]
GO
CREATE PROCEDURE sp_AddOrUpdateThirdPartyUrl(
	@Url varchar(100),
	@ActualUrl varchar(500),
	@Title nvarchar(200),
	@Description nvarchar(200),
	@Stamp datetime
)
AS
BEGIN
	IF EXISTS (SELECT * FROM [dbo].[ThirdPartyUrl] WHERE [Url]=@Url)
		UPDATE [dbo].[ThirdPartyUrl]
		SET [ActualUrl]=@ActualUrl, [LastUpdatedStamp]=@Stamp, [Title]=@Title, [Description]=@Description
		WHERE [Url]=@Url
	ELSE
		INSERT INTO [dbo].[ThirdPartyUrl]([Url],[ActualUrl],[Title],[Description],[CreatedStamp],[LastUpdatedStamp])
		VALUES(@Url,@ActualUrl,@Title,@Description,@Stamp,@Stamp);
END
GO

----- sp_GetThirdPartyUrl -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetThirdPartyUrl]'))
DROP PROCEDURE [dbo].[sp_GetThirdPartyUrl]
GO
CREATE PROCEDURE sp_GetThirdPartyUrl(
	@Url varchar(100)
)
AS
BEGIN
	SELECT [Url],[ActualUrl],[Title],[Description],[CreatedStamp],[LastUpdatedStamp] FROM [dbo].[ThirdPartyUrl];
END
GO

----- sp_AddOrUpdateFile -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddOrUpdateFile]'))
DROP PROCEDURE [dbo].[sp_AddOrUpdateFile]
GO
CREATE PROCEDURE [sp_AddOrUpdateFile](
	@Key uniqueidentifier,
	@SourceUrl varchar(500),
	@SourceName nvarchar(200),
	@FileName nvarchar(200),
	@FileExtension varchar(20),
	@FileSize int,
	@MD5 varchar(150),
	@Description nvarchar(200),
	@Tags nvarchar(200),
	@CreatedStamp datetime
)
AS
DECLARE @OriginalUrl AS varchar(500);
DECLARE @IsAdd AS bit;

BEGIN
	SET @IsAdd = 1;

	IF(@Key IS NOT NULL)
	BEGIN
		SELECT @OriginalUrl=[SourceUrl] FROM [dbo].[FileSystem] WHERE [Key]=@Key;
		IF(@OriginalUrl IS NOT NULL AND @OriginalUrl = @SourceUrl)
			SET @IsAdd = 0;
	END
	
	IF(@IsAdd = 1)
	BEGIN
		SET @Key = NEWID();
		INSERT INTO [dbo].[FileSystem]
           ([Key],[SourceUrl],[SourceName],[FileName],[FileExtension],[FileSize],[MD5],[Description],[Tags],[CreatedStamp])
		VALUES(@Key,@SourceUrl,@SourceName,@FileName,@FileExtension,@FileSize,@MD5,@Description,@Tags,@CreatedStamp);
	END
	ELSE
	BEGIN
		UPDATE [dbo].[FileSystem]
		SET [FileName] = @FileName, [Description] = @Description, [Tags] = @Tags
		WHERE [Key]=@Key;
	END
END
GO

----- sp_GetFile -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetFile]'))
DROP PROCEDURE [dbo].[sp_GetFile]
GO
CREATE PROCEDURE sp_GetFile(
	@Key uniqueidentifier
)
AS
BEGIN
	SELECT [Key],[SourceUrl],[SourceName],[FileName],[FileExtension],[FileSize],[MD5],[Description],[Tags],[CreatedStamp]
	FROM [dbo].[FileSystem]
	WHERE [Key] = @Key;
END
GO

----- sp_QueryFile -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryFile]'))
DROP PROCEDURE [dbo].[sp_QueryFile]
GO
CREATE PROCEDURE sp_QueryFile(
	@Keyword nvarchar(200),
	@FileMinSize int,
	@FileMaxSize int,
	@Tags nvarchar(200),
	@CreatedMinStamp datetime,
	@CreatedMaxStamp datetime
)
AS
DECLARE @SqlExp AS nvarchar(Max);
DECLARE @WhereExp AS nvarchar(Max);
DECLARE @TagOrExp AS nvarchar(Max);
DECLARE @KeywordOrExp AS nvarchar(Max);
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
DECLARE @Separator nvarchar(10);
DECLARE @SplitString nvarchar(max);
     
BEGIN
	SET @SqlExp = 'SELECT [Key],[SourceUrl],[SourceName],[FileName],[FileExtension],[FileSize],[MD5],[Description],[Tags],[CreatedStamp]
	FROM [dbo].[FileSystem]'
	
	SET @WhereExp = '';
	IF(@FileMinSize IS NULL OR @FileMinSize < 0)
		SET @FileMinSize = 0;
	SET @WhereExp = '[FileSize]>='+ CONVERT(varchar(20),@FileMinSize);

	IF(@FileMaxSize IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [FileSize]<' + CONVERT(varchar(20),@FileMaxSize);
	IF(@CreatedMinStamp IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [CreatedStamp]>=' + CONVERT(varchar(50),@CreatedMinStamp);
	IF(@CreatedMaxStamp IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [CreatedStamp]<' + CONVERT(varchar(50),@CreatedMaxStamp);
	IF(@Keyword IS NOT NULL AND @Keyword <> ' ')
	BEGIN
		SET @KeywordOrExp = '';
		SET @Separator = ',';
		SET @CurrentIndex=1;
		SET @SplitString = @Keyword;
		WHILE(@CurrentIndex<=len(@SplitString))
		BEGIN
			SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
				IF(@NextIndex=0 OR @NextIndex IS NULL)
					SELECT @NextIndex=len(@SplitString)+1;
				SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
				SET @KeywordOrExp = @KeywordOrExp + ' OR [SourceUrl] LIKE ''%' + @ReturnText
					+ '%'' OR [SourceName] LIKE ''%' + @ReturnText
					+ '%'' OR [FileName] LIKE ''%' + @ReturnText
					+ '%'' OR [FileExtension] LIKE ''%' + @ReturnText
					+ '%'' OR [Description] LIKE ''%' + @ReturnText + '%''';
			SET @CurrentIndex=@NextIndex+1;
		END
		
		IF(@KeywordOrExp <> '')
			SET @WhereExp = '(' + @WhereExp + ') AND (' + substring(@KeywordOrExp, 4, len(@KeywordOrExp))  + ')';
	END
	IF(@Tags IS NOT NULL AND @Tags <> '')
	BEGIN
		SET @TagOrExp = '';
		SET @Separator = ',';
		SET @CurrentIndex=1;
		SET @SplitString = @Tags;
		WHILE(@CurrentIndex<=len(@SplitString))
		BEGIN
			SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
				IF(@NextIndex=0 OR @NextIndex IS NULL)
					SELECT @NextIndex=len(@SplitString)+1;
				SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
				SET @TagOrExp = @TagOrExp + ' OR [Tags] LIKE ''%' + @ReturnText + ';%''';
			SET @CurrentIndex=@NextIndex+1;
		END
		
		IF(@TagOrExp <> '')
			SET @WhereExp = '(' + @WhereExp + ') AND (' + substring(@TagOrExp, 4, len(@TagOrExp))  + ')';
	END	
	
	SET @SqlExp = @SqlExp + ' WHERE ' + @WhereExp;
	exec (@SqlExp);
END
GO
